package com.javer.dao;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.List;

import org.springframework.jdbc.core.RowMapper;
import org.springframework.stereotype.Repository;
import org.springframework.util.Assert;

import com.javer.dao.support.DataSourceDaoSupport;
import com.javer.dao.support.ResultUtils;
import com.javer.domain.KeyVal;
import com.javer.domain.PageView;

@Repository
public class KeyValDao extends DataSourceDaoSupport {
	private static KeyValMapper mapper = new KeyValMapper();

	public PageView<KeyVal> getPageByKeyword(PageView pv, String keyword) {
		Object[] args = new Object[]{keyword};
		String sql1 = "select count(*) count from(select val from keyval where keyword = ? group by val) t";
		String sql2 = "select val,count(*) count from keyval where keyword = ? group by val order by count desc";
		return getByPage(pv, sql1, args, sql2, args, new RowMapper() {

			public Object mapRow(ResultSet rs, int rowNum) throws SQLException {
				KeyVal kv = new KeyVal();
				kv.setValue(rs.getString("val"));
				kv.setCount(rs.getInt("count"));
				return kv;
			}
			
		});
	}
	
	public List<KeyVal> getByKey(String key) {
		return jdbcTemplate.query("select * from keyval where keyword=?", new Object[]{key}, mapper);
	}
	
	@Override
	public RowMapper getMapper() {
		return mapper;
	}
	
	public KeyVal getById(int id) {
		return (KeyVal) jdbcTemplate.queryForObject("select * from keyval where id=" + id, mapper);
	}
	
	public List<KeyVal> getHotCompanyKeyword(String companyName) {
		return getHotCompanyKeyword(companyName, 10);
	}
	
	public List<KeyVal> getHotCompanyKeyword(String companyName, int limit) {
		String sql = "select keyword, val, count(*) count from keyval where keyword='company' and val like '%"+ companyName +"%' group by keyword,val order by count desc limit " + limit;
		return jdbcTemplate.query(sql, mapper);
	}
	
	public List<KeyVal> getKeyValByKeyword(String keyword) {
		String sql = "select keyword, val, count(*) count from keyval where keyword='"+keyword+"' group by keyword,val order by count desc limit 500";
		return jdbcTemplate.query(sql, mapper);
	}
	
	public void addKeyVal(KeyVal kv) {
		Assert.notNull(kv, "keyval object must not be null.");
		jdbcTemplate.update("insert into keyval(keyword,val) values(?,?)", new Object[]{kv.getKey(), kv.getValue()});
	}
	
	static class KeyValMapper implements RowMapper {

		public Object mapRow(ResultSet rs, int arg1) throws SQLException {
			KeyVal kv = new KeyVal();
			kv.setId(ResultUtils.getInt(rs, "id"));
			kv.setKey(rs.getString("keyword"));
			kv.setValue(rs.getString("val"));
			kv.setCount(ResultUtils.getInt(rs, "count"));
			return kv;
		}
		
	}
}
